﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
using AutoMapper;

namespace Data
{
    public class SachRepository
    {
        public List<SachDTO> LaySach() 
        {
            //DataTable table = new DataTable();
            List<SachDTO> list = new List<SachDTO>();
            QuanLyNhaSachEntities context = new QuanLyNhaSachEntities();
            var query = from sachitem in context.SachList
                        from tgitem in context.TacGiaList
                        from tlitem in context.TheLoaiList
                        where sachitem.IDTacGia == tgitem.IDTacGia && sachitem.IDTheLoai == tlitem.IDTheLoai
                        select new { sachitem.TenSach, tgitem.TenTacGia, tlitem.TenTheLoai };
            foreach (var item in query) 
            {
                SachDTO dto = new SachDTO();
                dto.TenSach = item.TenSach;
               // dto.TenTG = item.TenTacGia;
               // dto.TenTL = item.TenTheLoai;
                list.Add(dto);
            }
            return list;
        }

        /// <summary>
        /// select tat ca sach kem thong tin
        /// </summary>
        /// <returns></returns>
        public DataTable SelectSachDetail() 
        {
            DataTable table = new DataTable();
            try 
            {
                QuanLyNhaSachEntities context = new QuanLyNhaSachEntities();
                var query = from sachItem in context.SachList
                            from tacGiaItem in context.TacGiaList
                            from theLoaiItem in context.TheLoaiList
                            where sachItem.IDTacGia == tacGiaItem.IDTacGia
                                 && sachItem.IDTheLoai == theLoaiItem.IDTheLoai
                            select new 
                            {
                                sachItem.IDSach,
                                sachItem.MaSach,
                                sachItem.TenSach,
                                sachItem.SoLuongTon,
                                //sachItem.DonGiaSach,
                                tacGiaItem.TenTacGia,
                                theLoaiItem.TenTheLoai
                            };
                table.Columns.Add(SachComlumn.IDSach);
                table.Columns.Add(SachComlumn.MaSach);
                table.Columns.Add(SachComlumn.TenSach);
                table.Columns.Add(SachComlumn.SoLuongTon);
               // table.Columns.Add(SachComlumn.DonGiaSach);
                table.Columns.Add(TacGiaColumn.TenTacGia);
                table.Columns.Add(TheLoaiColumn.TenTheLoai);

                foreach (var item in query) 
                {
                    DataRow row = table.NewRow();
                    row[SachComlumn.IDSach] = item.IDSach;
                    row[SachComlumn.MaSach] = item.MaSach;
                    row[SachComlumn.TenSach] = item.TenSach;
                    row[SachComlumn.SoLuongTon] = item.SoLuongTon;
                   // row[SachComlumn.DonGiaSach] = item.DonGiaSach;
                    row[TacGiaColumn.TenTacGia] = item.TenTacGia;
                    row[TheLoaiColumn.TenTheLoai] = item.TenTheLoai;

                    table.Rows.Add(row);
                }
            }
            catch (System.Exception) 
            {
            }
            
            return table;
        }


        public DataTable TimKiem(string maSach, string tenSach, int idTacGia, int idTheLoai, int tonItHon, int tonNhieuHon)
        {
            DataTable table = new DataTable();
            try
            {
                QuanLyNhaSachEntities context = new QuanLyNhaSachEntities();

                var query = from sachItem in context.SachList
                            from tacGiaItem in context.TacGiaList
                            from theLoaiItem in context.TheLoaiList
                            where sachItem.IDTacGia == tacGiaItem.IDTacGia
                            && sachItem.IDTheLoai == theLoaiItem.IDTheLoai                            
                            select new 
                            {
                                sachItem.MaSach,
                                sachItem.TenSach,
                                theLoaiItem.TenTheLoai,
                                theLoaiItem.IDTheLoai,
                                tacGiaItem.TenTacGia, 
                                tacGiaItem.IDTacGia,
                                sachItem.DonGiaSach,
                                sachItem.SoLuongTon
                            };

                if(maSach != "")
                    query = query.Where(sachItem => sachItem.MaSach.Contains(maSach));
                if (tenSach != "")
                    query = query.Where(sachItem => sachItem.TenSach.Contains(tenSach));
                if (idTheLoai != -1)
                    query = query.Where(theLoaiItem => theLoaiItem.IDTheLoai == idTheLoai);
                if (idTacGia != -1)
                    query = query.Where(tacGiaItem => tacGiaItem.IDTacGia == idTacGia);
                if (tonItHon != -1)
                    query = query.Where(sachItem => sachItem.SoLuongTon < tonItHon);
                if (tonNhieuHon != -1)
                    query = query.Where(sachItem => sachItem.SoLuongTon > tonNhieuHon);
 
                    table.Columns.Add(SachComlumn.MaSach);
                    table.Columns.Add(SachComlumn.TenSach);
                    table.Columns.Add(TheLoaiColumn.TenTheLoai);
                    table.Columns.Add(TacGiaColumn.TenTacGia);                    
                    table.Columns.Add(SachComlumn.DonGiaSach);
                    table.Columns.Add(SachComlumn.SoLuongTon);

                    foreach (var item in query)
                    {
                        DataRow row = table.NewRow();
                        row[SachComlumn.MaSach] = item.MaSach;
                        row[SachComlumn.TenSach] = item.TenSach;
                        row[TheLoaiColumn.TenTheLoai] = item.TenTheLoai;
                        row[TacGiaColumn.TenTacGia] = item.TenTacGia;    
                        row[SachComlumn.DonGiaSach] = item.DonGiaSach;
                        row[SachComlumn.SoLuongTon] = item.SoLuongTon;
                        table.Rows.Add(row);
                    }
            }
            catch (System.Exception)
            {
            }
            return table;
        }

        public DataTable BaoCaoTon(int thang, int nam)
        {
            DataTable table = new DataTable();
            try
            {
                QuanLyNhaSachEntities context = new QuanLyNhaSachEntities();
                var query = from sachItem in context.SachList
                            from tg in context.TacGiaList
                            from tl in context.TheLoaiList
                            from hoaDonBanSachItem in context.HoaDonBanSachList
                            from bcTon in context.BaoCaoTonList
                            from ctBCTon in context.ChiTietBaoCaoTonList                            
                            where sachItem.IDTacGia == tg.IDTacGia && sachItem.IDTheLoai == tl.IDTheLoai
                                    && sachItem.IDSach == ctBCTon.IDSach && ctBCTon.IDBaoCaoTon == bcTon.IDBaoCaoTon     
                                    && bcTon.Thang.Month == thang && bcTon.Thang.Year == nam
                            //group sachItem by sachItem.TenSach into g
                            select new
                            {
                                sachItem.MaSach,// = g.Key,
                                sachItem.TenSach,
                                tg.TenTacGia,
                                tl.TenTheLoai,
                                ctBCTon.TonDau,
                                ctBCTon.PhatSinh,
                                ctBCTon.TonCuoi
                            };

                
                table.Columns.Add(SachComlumn.TenSach);
                table.Columns.Add(TacGiaColumn.TenTacGia);
                table.Columns.Add(TheLoaiColumn.TenTheLoai);
                table.Columns.Add(ChiTietBaoCaoTonColumn.TonDau);
                table.Columns.Add(ChiTietBaoCaoTonColumn.TonCuoi);
                table.Columns.Add(ChiTietBaoCaoTonColumn.PhatSinh);

                foreach (var item in query)
                {
                    DataRow row = table.NewRow();
                    row[SachComlumn.TenSach] = item.TenSach;
                    row[TacGiaColumn.TenTacGia] = item.TenTacGia;
                    row[TheLoaiColumn.TenTheLoai] = item.TenTheLoai;
                    row[ChiTietBaoCaoTonColumn.TonDau] = item.TonDau;
                    row[ChiTietBaoCaoTonColumn.TonCuoi] = item.TonCuoi;
                    row[ChiTietBaoCaoTonColumn.PhatSinh] = item.PhatSinh;
                    table.Rows.Add(row);
                }

            }
            catch (System.Exception)
            {

            }
            return table;
        }

      
        /// <summary>
        /// lấy danh sách sách - id, mã, tên, số lượng tồn, đơn giá, tên tác giả, tên thể loại
        /// </summary>
        /// <returns>table</returns>
        public DataTable GetSachListDetail() {
            DataTable table = new DataTable();
            try {
                QuanLyNhaSachEntities context = new QuanLyNhaSachEntities();
                var query = from sachItem in context.SachList
                            from tacGiaItem in context.TacGiaList
                            from theLoaiItem in context.TheLoaiList
                            where sachItem.IDTacGia == tacGiaItem.IDTacGia
                                 && sachItem.IDTheLoai == theLoaiItem.IDTheLoai
                            select new {
                                sachItem.IDSach,
                                sachItem.MaSach,
                                sachItem.TenSach,
                                sachItem.SoLuongTon,
                                sachItem.DonGiaSach,
                                tacGiaItem.TenTacGia,
                                theLoaiItem.TenTheLoai
                            };
                table.Columns.Add(SachComlumn.IDSach);
                table.Columns.Add(SachComlumn.MaSach);
                table.Columns.Add(SachComlumn.TenSach);
                table.Columns.Add(SachComlumn.SoLuongTon);
                table.Columns.Add(SachComlumn.DonGiaSach);
                table.Columns.Add(TacGiaColumn.TenTacGia);
                table.Columns.Add(TheLoaiColumn.TenTheLoai);

                foreach (var item in query) {
                    DataRow row = table.NewRow();
                    row[SachComlumn.IDSach] = item.IDSach;
                    row[SachComlumn.MaSach] = item.MaSach;
                    row[SachComlumn.TenSach] = item.TenSach;
                    row[SachComlumn.SoLuongTon] = item.SoLuongTon;
                    row[SachComlumn.DonGiaSach] = item.DonGiaSach;
                    row[TacGiaColumn.TenTacGia] = item.TenTacGia;
                    row[TheLoaiColumn.TenTheLoai] = item.TenTheLoai;

                    table.Rows.Add(row);
                }
            }
            catch (System.Exception) {
            }
            
            return table;
        }
		
		public bool AddNewBook(SachDTO value){
			try{
				QuanLyNhaSachEntities context = new QuanLyNhaSachEntities();
                SACH s = new SACH();

                Mapper.CreateMap<SachDTO, SACH>();
                s = Mapper.Map<SachDTO, SACH>(value);
                context.AddToSachList(s);

                context.SaveChanges();
				return true;
			}
			catch{
				return false;
			}
		}

        public string CreateMaSach(int theLoaiID) {

            return "";
        }

        public string CreateMaSach(string maTheLoai) {
            QuanLyNhaSachEntities context = new QuanLyNhaSachEntities();
            //indentify ID
            var maxID = (from max in context.GetIndentity("SACH")
                         select max.CurrentIndentity).FirstOrDefault();
            if (maxID == 1)
                maxID = 0;
            
            return maTheLoai + (maxID + 1);            
       }
        
    }
}
